Вчерашний запрос, "заморозивший" миграцию на три часа:
SELECT entity_id, MAX(start_date), MAX(end_date) FROM ( SELECT entity_id, MAX(start_date) start_date, NULL AS end_date FROM audit_log WHERE attr = 'reviewed' AND newValue = 'true' GROUP BY entity_id UNION ALL SELECT entity_id, NULL AS start_date, MAX(end_date) end_date FROM audit_log WHERE attr = 'overriden' AND newValue = 'true' GROUP BY entity_id ) a GROUP BY entity_id;
можно было бы написать сильно оптимальнее вот так:
SELECT entity_id, MAX(if(attr = 'reviewed', NULL, start_date)) AS start_date, MAX(if(attr = 'reviewed', end_date, NULL)) end_date FROM audit_log WHERE (attr = 'reviewed' OR attr='overriden') AND newValue = 'true' GROUP BY entity_id;
При сильно похожих WHERE в таких конструкциях, удобнее схлопнуть UNION и "на лету" подставлять в SELECT нужное значение через if.
Вчерашний запрос, "заморозивший" миграцию на три часа:
SELECT entity_id, MAX(start_date), MAX(end_date) FROM ( SELECT entity_id, MAX(start_date) start_date, NULL AS end_date FROM audit_log WHERE attr = 'reviewed' AND newValue = 'true' GROUP BY entity_id UNION ALL SELECT entity_id, NULL AS start_date, MAX(end_date) end_date FROM audit_log WHERE attr = 'overriden' AND newValue = 'true' GROUP BY entity_id ) a GROUP BY entity_id;
можно было бы написать сильно оптимальнее вот так:
SELECT entity_id, MAX(if(attr = 'reviewed', NULL, start_date)) AS start_date, MAX(if(attr = 'reviewed', end_date, NULL)) end_date FROM audit_log WHERE (attr = 'reviewed' OR attr='overriden') AND newValue = 'true' GROUP BY entity_id;
При сильно похожих WHERE в таких конструкциях, удобнее схлопнуть UNION и "на лету" подставлять в SELECT нужное значение через if.
BY Developer's mind
Warning: Undefined variable $i in /var/www/tg-me/post.php on line 283
Telegram is riding high, adding tens of million of users this year. Now the bill is coming due.Telegram is one of the few significant social-media challengers to Facebook Inc., FB -1.90% on a trajectory toward one billion users active each month by the end of 2022, up from roughly 550 million today.
Among the actives, Ascendas REIT sank 0.64 percent, while CapitaLand Integrated Commercial Trust plummeted 1.42 percent, City Developments plunged 1.12 percent, Dairy Farm International tumbled 0.86 percent, DBS Group skidded 0.68 percent, Genting Singapore retreated 0.67 percent, Hongkong Land climbed 1.30 percent, Mapletree Commercial Trust lost 0.47 percent, Mapletree Logistics Trust tanked 0.95 percent, Oversea-Chinese Banking Corporation dropped 0.61 percent, SATS rose 0.24 percent, SembCorp Industries shed 0.54 percent, Singapore Airlines surrendered 0.79 percent, Singapore Exchange slid 0.30 percent, Singapore Press Holdings declined 1.03 percent, Singapore Technologies Engineering dipped 0.26 percent, SingTel advanced 0.81 percent, United Overseas Bank fell 0.39 percent, Wilmar International eased 0.24 percent, Yangzijiang Shipbuilding jumped 1.42 percent and Keppel Corp, Thai Beverage, CapitaLand and Comfort DelGro were unchanged.
